package com.navi.loaddata.loadhis;

import com.alibaba.fastjson.JSONObject;
import com.navi.loaddata.core.gpEntity.WppCdefectGlassF;
import com.navi.loaddata.core.gpEntity.WppFdefectF;
import com.navi.loaddata.core.gpEntity.WppFdefectPanelF;
import com.navi.loaddata.core.mysqlEntity.QrtzEtlDetail;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.utils.JacksonUtil;
import com.navi.loaddata.utils.SaveEtlPoint;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Query;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadSpecialOpeYeildData {
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Autowired
    private GreenPlumRepositoryImpl dao2;

    @Autowired
    private MySqlRepositoryImpl mysqlDao;

    @Autowired
    private SaveEtlPoint saveEtlPoint;


    //@Scheduled(fixedDelay = 1000000)
    @Transactional(value = "secondaryPlatformTransactionManager")
    public void loadprodHis() {
        /**
        String sqlsql = ""
+"        SELECT   "
+"        glassF.glass_id,   "
+"                glassF.ope_id,   "
+"                glassF.evt_timestamp   "
+"        FROM   "
+"        wpp_fdefect_glass_f glassF   "
+"        INNER JOIN (   "
+"                SELECT   "
+"        glass_id,   "
+"                ope_id,   "
+"                MAX (sdf.evt_timestamp) AS maxEvtTime,   "
+"        MIN (sdf.evt_timestamp) AS minEvtTime   "
+"        FROM   "
+"                (   "
+"                        SELECT   "
+"                        glass1.glass_id,   "
+"                        glass1.evt_timestamp,   "
+"                        glass1.real_flg,   "
+"                        glass1.ope_id,   "
+"                        glass1.eqpt_id,   "
+"                        glass1.jge_cnt,   "
+"                        glass1.etl_timestamp,   "
+"                        glass1.db_timestamp,   "
+"                        glass1.file_url   "
+"                        FROM   "
+"                                (   "
+"                                        SELECT   "
+"                                        glass_id,   "
+"                                        evt_timestamp,   "
+"                                        real_flg,   "
+"                                        ope_id,   "
+"                                        eqpt_id,   "
+"                                        jge_cnt,   "
+"                                        etl_timestamp,   "
+"                                        db_timestamp,   "
+"                                        file_url   "
+"                                        FROM   "
+"                                        wpp_fdefect_glass_f   "
+"                                        WHERE   "
+"                                        1 = 1   "
+"                                        AND real_flg = 'Y'   "
+"                                        AND evt_timestamp <= '2018-03-16'   "
+"                                ) glass1   "
+"                        INNER JOIN (   "
+"                        SELECT   "
+"                        result1.glass_id2 AS glass_id22,   "
+"                        result1.ope_id2 AS ope_id22,   "
+"                        result1.glass_cnt2 AS glass_cnt22   "
+"                        FROM   "
+"                                (   "
+"                                        SELECT   "
+"                                        glass_id AS glass_id2,   "
+"                                        ope_id AS ope_id2,   "
+"                                        COUNT (glass_id) AS glass_cnt2   "
+"                                        FROM   "
+"                                        wpp_fdefect_glass_f   "
+"                                        GROUP BY   "
+"                                        glass_id,   "
+"                                        ope_id,   "
+"                                        real_flg   "
+"                                        HAVING   "
+"                                        real_flg = 'Y'   "
+"                                ) result1   "
+"                        WHERE   "
+"                        result1.glass_cnt2 > 1   "
+"                        ) glass2 ON glass1.glass_id = glass2.glass_id22   "
+"                        AND glass1.ope_id = glass2.ope_id22   "
+"                        ORDER BY   "
//+"                                -- 					glass_id,   "
//+"                        -- 					ope_id   "
+"                        evt_timestamp   "
+"                ) sdf   "
+"        GROUP BY   "
+"        glass_id,   "
+"                ope_id   "
+") realflgErrorInfo ON glassF.glass_id = realflgErrorInfo.glass_id   "
+"        AND glassF.ope_id = realflgErrorInfo.ope_id   "
+"        AND glassF.evt_timestamp < realflgErrorInfo.maxEvtTime   "
+"        AND glassF.real_flg = 'Y'   "
+"        AND glassF.evt_timestamp >= realflgErrorInfo.minEvtTime   ";
        List<Object[]> objList = dao2.findBySQL(sqlsql);
        int count = 0;
        for (int i=0;i<objList.size();i++){
            String glassId = objList.get(i)[0].toString();
            String opeId = objList.get(i)[1].toString();
            String evtTimestamp = objList.get(i)[2].toString();
            String sqlsql2 = " UPDATE wpp_fdefect_glass_f SET real_flg = 'N' where glass_id='"+glassId+"' and ope_id='"+opeId+"' and evt_timestamp='"+evtTimestamp+"'";
            Query query2 = dao2.createNativeQuery(sqlsql2);
            query2.executeUpdate();
            //                           TODO:   pattern [C]
            String sqlsql3 = " UPDATE wpp_fdefect_panel_f SET real_flg = 'N' where glass_id='"+glassId+"' and ope_id='"+opeId+"' and evt_timestamp='"+evtTimestamp+"'";
            Query query3 = dao2.createNativeQuery(sqlsql3);
            query3.executeUpdate();
            String sqlsql4 = " UPDATE wpp_fdefect_f SET real_flg = 'N' where glass_id='"+glassId+"' and ope_id='"+opeId+"' and evt_timestamp='"+evtTimestamp+"'";
            Query query4 = dao2.createNativeQuery(sqlsql4);
            query4.executeUpdate();
            count += 1;
        }
        System.out.println("已修改记录:"+count); */



        /*List<WppFdefectPanelF> panelList22 = dao2.list(WppFdefectPanelF.class,"FROM WppFdefectPanelF WHERE opeId ='C1300' and glassId = 'C17BO003AX' and panelId = 'C17BO003AX01' and jgeCnt = '1'");
        WppFdefectPanelFNoRealFlg glassEntity22 = new WppFdefectPanelFNoRealFlg();
        BeanUtils.copyProperties(panelList22.get(0), glassEntity22);
        glassEntity22.setUnqSeqId("1111111111111");
        glassEntity22.setOpeTblName("wpp_fdefect_panel_f");
        rabbitTemplate.convertAndSend("qmsExchange", "F_PANEL", JacksonUtil.toJSONStr(glassEntity22).getBytes());
        System.out.println("执行完成~");*/

        /*

        StringBuffer hqlBuffer = new StringBuffer("From QrtzEtlDetail WHERE projectName='").append("loaddata").append("'");
        hqlBuffer.append(" and schedName = '").append("LoadSpecialOpeYeildData").append("'");
        hqlBuffer.append(" and instanceName = '").append("wpp_fdefect_panel_f,wpp_fdefect_defect_f").append("'");
        List<QrtzEtlDetail> qrtzEtlDetailList = mysqlDao.find(QrtzEtlDetail.class, hqlBuffer.toString());

        Timestamp startTime = qrtzEtlDetailList.get(0).getCheckinTime();
        Timestamp endTime = qrtzEtlDetailList.get(0).getEndTime();
        String relFlg = qrtzEtlDetailList.get(0).getRelFlg();
        Integer cnt = 0;

        if (endTime.getTime() <= new Date().getTime()-20000 && relFlg.equalsIgnoreCase("Y")){
            //System.out.println("90/9I数据查询中..."+Timestamp.valueOf(dateFormat.format(new Date())));
            List<WppFdefectPanelF> panelList = dao2.list(WppFdefectPanelF.class,"FROM WppFdefectPanelF WHERE opeId in('C4590','C459I','C7590','C759I','C1590','C159I') and evtTimestamp<='" + Timestamp.valueOf(dateFormat.format(new Date(new Date().getTime()+60000*60*24*5))) + "' and dbTimestamp>='" + startTime + "' and dbTimestamp<='" + endTime + "' and realFlg='Y'");
            List<WppFdefectF> defectList = dao2.list(WppFdefectF.class, "             FROM WppFdefectF WHERE opeId in('C4590','C459I','C7590','C759I','C1590','C159I') and evtTimestamp<='" + Timestamp.valueOf(dateFormat.format(new Date(new Date().getTime()+60000*60*24*5))) + "' and dbTimestamp>='" + startTime + "' and dbTimestamp<='" + endTime + "' and realFlg='Y'");
            try {
                System.out.println("60Panel数据查询中..."+Timestamp.valueOf(dateFormat.format(new Date())));
                if (CollectionUtils.isNotEmpty(panelList)) cnt = panelWork(panelList, Timestamp.valueOf(dateFormat.format(new Date(startTime.getTime()-60000*60*24*20))), endTime, cnt);
                System.out.println("60Defect数据查询中..."+Timestamp.valueOf(dateFormat.format(new Date())));
                if (CollectionUtils.isNotEmpty(defectList)) cnt = defectWork(defectList, Timestamp.valueOf(dateFormat.format(new Date(startTime.getTime()-60000*60*24*20))), endTime, cnt);
                saveEtlPoint.save2etlSpecialOpe(endTime,cnt);
                System.out.println("[63-90-9ILog]"+Timestamp.valueOf(dateFormat.format(new Date()))+"   修改数据:"+cnt+"   【StartTime:"+startTime+" -- "+"EndTime:"+endTime+"】");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        */
    }

    public Integer panelWork(List<WppFdefectPanelF> dataList, Timestamp startTime, Timestamp endTime, Integer cnt){
        Map<String, List<WppFdefectPanelF>> byOpeMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getOpeId()));
        String byOpeSql = "(";
        for (String opeStr:byOpeMap.keySet()){
            byOpeSql += ("'"+opeStr.substring(0,3)+"63',");
        }
        if (byOpeSql.endsWith(",")) byOpeSql = byOpeSql.substring(0,byOpeSql.length()-1);
        byOpeSql += ")";

        Map<String, List<WppFdefectPanelF>> byGlassMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getGlassId()));
        String byGlassSql = "(";
        for (String glassStr:byGlassMap.keySet()){
            byGlassSql += ("'"+glassStr+"',");
        }
        if (byGlassSql.endsWith(",")) byGlassSql = byGlassSql.substring(0,byGlassSql.length()-1);
        byGlassSql += ")";

        Map<String, List<WppFdefectPanelF>> byPanelMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getPanelId()));
        String byPanelSql = "(";
        for (String panelStr:byPanelMap.keySet()){
            byPanelSql += ("'"+panelStr+"',");
        }
        if (byPanelSql.endsWith(",")) byPanelSql = byPanelSql.substring(0,byPanelSql.length()-1);
        byPanelSql += ")";

        String sql = "FROM WppFdefectPanelF WHERE opeId in" + byOpeSql + " and glassId in"+byGlassSql+" and panelId in" + byPanelSql + " and evtTimestamp<='" + Timestamp.valueOf(dateFormat.format(new Date(new Date().getTime()+60000*60*24*5))) + "' and dbTimestamp>='" + startTime + "' and dbTimestamp<='" + endTime + "' and realFlg='Y'";
        List<WppFdefectPanelF> selectDataList = dao2.list(WppFdefectPanelF.class, sql);
        if (CollectionUtils.isNotEmpty(selectDataList)) {
            List<WppFdefectPanelF> updateDataList = new ArrayList<WppFdefectPanelF>();
            for (WppFdefectPanelF selectData:selectDataList){
                for (WppFdefectPanelF data:dataList){
                    if (selectData.getOpeId().substring(0,3).equals(data.getOpeId().substring(0,3)) && selectData.getGlassId().equals(data.getGlassId())
                            && selectData.getPanelId().equals(data.getPanelId())){
                        /**
                         * sourcePnlJge;
                         * sourcePnlRejType;
                         */
                        //String updateSql = "update wpp_fdefect_panel_f set source_pnl_jge = '" + selectData.getPnlJge() + "',source_pnl_rej_type = '" + selectData.getPnlRejType()
                        //        + "' where ope_id='" + data.getOpeId() + "' and panel_id='" + data.getPanelId() + "' and glass_id='" + data.getGlassId() + "' and evt_timestamp='"
                        //        + data.getEvtTimestamp() + "' and real_flg='Y'";
                        //JSONObject updateJsonObj = new JSONObject();
                        //updateJsonObj.put("real_flg", updateSql);
                        //updateJsonObj.put("ope_tbl_name", "wpp_fdefect_panel_f");
                        //rabbitTemplate.convertAndSend("qmsExchange", "WPP_F_SPECIAL_OPE_YEILD_LOAD", updateJsonObj.toJSONString());
                        WppFdefectPanelF dataTmp = new WppFdefectPanelF();
                        BeanUtils.copyProperties(data, dataTmp);
                        dataTmp.setSourcePnlJge(selectData.getPnlJge());
                        dataTmp.setSourcePnlRejType(selectData.getPnlRejType());
                        //TODO:判断realFlg是否有变动
                        dataTmp.setOpeTblName("wpp_fdefect_panel_f");
                        updateDataList.add(dataTmp);
                        cnt += 1;
                    }
                }
            }
            rabbitTemplate.convertAndSend("qmsExchange", "WPP_F_SPECIAL_OPE_YEILD_LOAD", JacksonUtil.toJSONStr(updateDataList).getBytes());
        }
        return cnt;

    }

    public Integer defectWork(List<WppFdefectF> dataList, Timestamp startTime, Timestamp endTime, Integer cnt) {
        Map<String, List<WppFdefectF>> byOpeMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getOpeId()));
        String byOpeSql = "(";
        for (String opeStr : byOpeMap.keySet()) {
            byOpeSql += ("'"+opeStr.substring(0,3)+"63',");
        }
        if (byOpeSql.endsWith(",")) byOpeSql = byOpeSql.substring(0,byOpeSql.length()-1);
        byOpeSql += ")";

        Map<String, List<WppFdefectF>> byGlassMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getGlassId()));
        String byGlassSql = "(";
        for (String glassStr : byGlassMap.keySet()) {
            byGlassSql += ("'" + glassStr + "',");
        }
        if (byGlassSql.endsWith(",")) byGlassSql = byGlassSql.substring(0,byGlassSql.length()-1);
        byGlassSql += ")";

        Map<String, List<WppFdefectF>> byPanelMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getPanelId()));
        String byPanelSql = "(";
        for (String panelStr : byPanelMap.keySet()) {
            byPanelSql += ("'" + panelStr + "',");
        }
        if (byPanelSql.endsWith(",")) byPanelSql = byPanelSql.substring(0,byPanelSql.length()-1);
        byPanelSql += ")";
        /*Map<String, List<WppFdefectF>> byDefectMap = dataList.stream().collect(Collectors.groupingBy(data -> data.getDefectNo()));
        String byDefectSql = "(";
        for (String defectStr:byDefectMap.keySet()){
            byDefectSql += ("'"+defectStr+"',");
        }
        if (byDefectSql.endsWith(",")) byDefectSql = byDefectSql.substring(0,byDefectSql.length()-1);
        byDefectSql += ")";*/
        String sql = "FROM WppFdefectF WHERE opeId in" + byOpeSql + " and glassId in" + byGlassSql + " and panelId in" + byPanelSql + " and evtTimestamp<='" + Timestamp.valueOf(dateFormat.format(new Date(new Date().getTime()+60000*60*24*5))) + "' and dbTimestamp>='" + startTime + "' and dbTimestamp<='" + endTime + "' and realFlg='Y'";
        List<WppFdefectF> selectDataList = dao2.list(WppFdefectF.class, sql);
        if (CollectionUtils.isNotEmpty(selectDataList)) {
            List<WppFdefectF> updateDataList = new ArrayList<WppFdefectF>();
            for (WppFdefectF selectData : selectDataList) {
                for (WppFdefectF data : dataList) {
                    if (selectData.getOpeId().substring(0,3).equals(data.getOpeId().substring(0,3)) && selectData.getGlassId().equals(data.getGlassId()) && selectData.getPanelId().equals(data.getPanelId())
                            && selectData.getDefectNo() == data.getDefectNo()) {
                        /**
                         * sourceDefectJge;
                         * sourceDefefctCode;
                         */
                        //String updateSql = "update wpp_fdefect_f set source_defect_jge = '" + selectData.getDefectFlg() + "',source_defefct_code = '" + selectData.getDefectCode()
                        //        + "' where ope_id='" + data.getOpeId() + "' and panel_id='" + data.getPanelId() + "' and glass_id='" + data.getGlassId() + "' and evt_timestamp='"
                        //        + data.getEvtTimestamp() + "' and defect_no='" + data.getDefectNo() + "' and real_flg='Y'";
                        //JSONObject updateJsonObj = new JSONObject();
                        //updateJsonObj.put("real_flg", updateSql);
                        //updateJsonObj.put("ope_tbl_name", "wpp_fdefect_f");
                        //rabbitTemplate.convertAndSend("qmsExchange", "WPP_F_SPECIAL_OPE_YEILD_LOAD", updateJsonObj.toJSONString());
                        WppFdefectF dataTmp = new WppFdefectF();
                        BeanUtils.copyProperties(data, dataTmp);
                        dataTmp.setSourceDefectJge(selectData.getDefectFlg());
                        dataTmp.setSourceDefefctCode(selectData.getDefectCode());
                        //TODO:判断realFlg是否有变动
                        dataTmp.setOpeTblName("wpp_fdefect_f");
                        updateDataList.add(dataTmp);
                        cnt += 1;
                    }
                }
            }
            rabbitTemplate.convertAndSend("qmsExchange", "WPP_F_SPECIAL_OPE_YEILD_LOAD", JacksonUtil.toJSONStr(updateDataList).getBytes());
        }
        return cnt;
    }
}
